﻿-- =============================================
-- Author:		<Author,XFL>
-- Edit date: <Create Date,2012 02 01>
-- Description:	<Description, [人资管理]-[业绩绩效考核]-[销售业绩]
--	将原来的存储过程进行了优化,并且提速效果很明显
--	同时,部门查询是 使用了部门编号查询 BMBH >
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Jixiao_TongjiOne_BackUp0306]
	(
		@companyId int,
		@departmentId int,
		--@EmpState int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@EName nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
begin

		declare @bmbh_T  varchar(50)
		set @bmbh_T = (select bmbh  FROM department WHERE id=@departmentId)		
		declare @indextable table(id int identity(1,1),nid int)
		insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;

		declare @list_table_1 table(
		id int identity(1,1)
		,Eid int
		,username nvarchar(50)
		,CompanyID int /*公司名称*/
		,DepartmentID int /*部门名称*/
		,EName nvarchar(50) /*部门名称*/
		,Order_Count int	
		,Order_FullPrice Decimal
		)


		;WITH list As 
		(
		select 
		id,username
		,CompanyId
		,DepartmentId
		,EName
		,(
		Select count(0) From [project] 
		Where [project].salesmanaccount=e.username 
		and [project].salesmanaccount <>'' 
		and Convert(nchar(10),[project].CreateDate,120) between @StartDate and @EndDate
		) As Order_Count

		,ISNULL((

		Select sum([project].saleprice) From [project] 
		Where [project].salesmanaccount=e.username 
		and [project].salesmanaccount <>'' 
		and Convert(nchar(10),[project].CreateDate,120) between @StartDate and @EndDate


		),0) As Order_FullPrice
		,(Case When State<>5 Then (select  1 ) Else ( select  2  ) End) as state
		From Employee e
		Where (@CompanyId=0 Or e.CompanyId=@companyId) 
		and (@DepartmentId=0 Or DepartmentId in (select c.nid from @indextable c)) 
		and (@EName is null Or e.EName like '%'+@EName+'%') 
		and e.username <>'' and state <> 5
		)


		insert into @list_table_1(
		Eid,username
		,CompanyId
		,DepartmentId
		,ename 
		,Order_Count 
		,Order_FullPrice 
		) 
		select 
		id,username
		,CompanyId
		,DepartmentId
		,ename 
		,Order_Count 
		,Order_FullPrice 
		From list  
       -- where ( @EmpState =0 or list.state = @EmpState)
		order by Order_FullPrice desc , ename;

		-- 上面完成 list 表
		-- Select * from @list_table_1 

		declare @A int,@B int,@C Decimal
		set @A =(select count(0) from @list_table_1 )
		set @B =(select sum(Order_Count) from @list_table_1 ) 
		set @C=(select sum(Order_FullPrice) from @list_table_1 ) 

	declare @list_table_2 table(
		id int identity(1,1)
		,Row int
		,Eid int
		,username nvarchar(50)
		,CompanyId int /*公司名称ID*/
		,DepartmentId int /*部门名称ID*/
		,EName nvarchar(50)
		,Order_Count int	
		,Order_FullPrice Decimal
		)

		;WITH list_return as (
		Select ROW_NUMBER() OVER (ORDER BY Id) AS Row
		,eid as id,username
		,CompanyId
		,DepartmentId 
		,ename 
		,Order_Count 
		,Order_FullPrice 
		from @list_table_1
		)

		insert into @list_table_2(
		Row
		,Eid 
		,username
		,CompanyId
		,DepartmentId
		,EName
		,Order_Count 
		,Order_FullPrice 

		)
		Select 
		Row
		,id
		,username
		,CompanyId
		,DepartmentId
		,ename 
		,Order_Count 
		,Order_FullPrice 

		From list_return 
		Where Row between @StartIndex and @EndIndex

		ORDER BY Row;





		select  
		Row	
		,Eid as id  
		,username 
		,(Select CompanyName From Company Where Id=e.CompanyId) As Company
		,(Select DName From Department Where Id=e.DepartmentId) As Department
		,EName 
		,Order_Count 
		,Order_FullPrice 
		,ISNULL((Select sum([project].SalePrice-[project].CostPrice) From [project] Where [project].salesmanaccount=E.UserName and [project].salesmanaccount <>'' and Convert(nchar(10),[project].CreateDate,120) between @StartDate and @EndDate),0) As Maoli
		,ISNULL((select top 1 [Employee].state from [Employee] Where [Employee].UserName=[E].UserName ),0) as State
		,@A as Count_Sum 
		,@B as Order_Count_Sum 
		,@C as Order_FullPrice_Sum 
		--

		from @list_table_2 e
		ORDER BY Row



end
	RETURN
